﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using Lamex.Library;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraGrid.Columns;
using System.Data.SqlClient;

namespace Lamex.mLabor.UI
{
    public partial class frmLBR_LaborTime_Maintain : frmBaseChild
    {
        string strSql, CurUser;
        DataTable dt;
        string cdtSubFactory, cdtWorkStation, cdtWorkCenter;
        string strCurSubFactory;
        string conStr = @"data source=172.23.134.36;initial catalog=LamexMESManager;user id=sa;password=Sa@1050%";

        public frmLBR_LaborTime_Maintain()
        {
            InitializeComponent();
        }

        private void frmLBR_Working_Hours_Resize(object sender, EventArgs e)
        {
            splitContainer1.SplitterDistance = splitContainer1.Width / 2;
        }

        private void frmLBR_Working_Hours_Load(object sender, EventArgs e)
        {
            setCurSubFactory();
            dteSelDate.EditValue = DateTime.Now;
        }

        private void setCurSubFactory()
        {
            CurUser = Lamex.Common.Loginer.CurrentUser.Account;
            switch (CurUser)
            {
                case "Rocky He":
                    comboBox1.Enabled = true; 
                    comboBox1.Text = "生产-桌子";
                    break;
                case "D":
                    comboBox1.Text = "生产-桌子";
                    break;
                case "S":
                    comboBox1.Text = "生产-椅子";
                    break;
                case "P":
                    comboBox1.Text = "生产-屏风";
                    break;
                case "M":
                    comboBox1.Text = "生产-MCG";
                    break;
                default:
                    comboBox1.Text = "";
                    break;
            }
        }
        
        #region 选项改变界面初始化

        private void rbtnWorkCenter_CheckedChanged(object sender, EventArgs e)
        {
            btnInsert.Enabled = false;
            gridView1.Columns.Clear();
            gridView2.Columns.Clear();
        }

        private void rbtnWorkstation_CheckedChanged(object sender, EventArgs e)
        {
            btnInsert.Enabled = false;
            gridView1.Columns.Clear();
            gridView2.Columns.Clear();
        }

        private void rbtnSubFactory_CheckedChanged(object sender, EventArgs e)
        {
            btnInsert.Enabled = false;
            gridView1.Columns.Clear();
            gridView2.Columns.Clear();
        }

        #endregion

        //设置可编辑数据列，设置特定列可编辑，必须在加载数据后执行
        private void setGridViewEditaleColumn()
        {
            gridView1.OptionsBehavior.Editable = true;
            gridView1.OptionsBehavior.ReadOnly = false;

            foreach (GridColumn c in gridView1.Columns)
            {
                if (c.Name == "col实际工时")
                {
                    c.OptionsColumn.ReadOnly = false;
                    c.OptionsColumn.AllowEdit = true;
                }
                else
                {
                    c.OptionsColumn.ReadOnly = true;
                    c.OptionsColumn.AllowEdit = false;
                }
            }
        }

        //总原则：Pxx工序不可以出现在Dxx站点及分厂中，
        //具体来说表【OP_MGT_Workstation_Process】不再维护P79配件，其人工工时并入D54中
        //所有工时查询中涉及P79的转成D54桌子配件，合并处理
        private void btnCopy_Click(object sender, EventArgs e)
        {   
            if (isExistCurDateData())
            {
                if (MessageBox.Show("数据已存在！" + "\r\n" + "是否重新录入实际工时数据？", "工时数据复制确认", MessageBoxButtons.OKCancel) == DialogResult.OK)
                {
                    goto c;
                }
                else
                {
                    return;
                }
            }
        c:;
            //复制参考数据
            copyDataAndSetData_V1("'" + dteSelDate.Text.ToString() + "'", "'9999-12-31'", "'" + CurUser + "'");
            //刷新参考数据
            copyDataAndSetData_V2("WORKING_DATE");

            setGridViewEditaleColumn();//设置可编辑列，必须在加载数据后执行
            btnInsert.Enabled = true;
        }

        //检查所选日期是否已维护
        private bool isExistCurDateData()
        {
            strCurSubFactory = "";
            if (comboBox1.SelectedIndex != -1)
            {
                strCurSubFactory = comboBox1.SelectedItem.ToString();
            }

            getQueyCondition();

            bool bl = false;

            strSql = getSqlString_Check("HR_Work_Hours_By_SubFactory_Cancel", cdtSubFactory);
            dt = Lamex.Base.BaseBuildSql.GetDataTableBySql(strSql);
            if (dt.Rows.Count > 0) { bl = true; }

            strSql = getSqlString_Check("HR_Work_Hours_By_Workstation_Cancel", cdtWorkStation);
            dt = Lamex.Base.BaseBuildSql.GetDataTableBySql(strSql);
            if (dt.Rows.Count > 0) { bl = true; }

            strSql = getSqlString_Check("HR_Work_Hours_By_WorkCenter_Cancel", cdtWorkCenter);
            dt = Lamex.Base.BaseBuildSql.GetDataTableBySql(strSql);
            if (dt.Rows.Count > 0) { bl = true; }

            return bl;            
        }

        private string getSqlString_Check(string strDataTable, string strCondition)
        {
            strSql = @"  SELECT * 
                      FROM " + strDataTable + @"
                      WHERE " + strCondition + @" AND WORKING_DATE='" + dteSelDate.Text.ToString() + @"'";
            return strSql;
        }

        private void copyDataAndSetData_V1(string strDate_Type, string strUpdate_Time, string strUpdate_User)
        {
            if (rbtnSubFactory.Checked == true)
            {
                //DateTime.Now.ToString("yyyy/M/d")
                strSql = getSqlString_12("HR_Work_Hours_By_SubFactory_Cancel", "SUB_FACTORY AS 分厂,", cdtSubFactory, strDate_Type, strUpdate_Time, strUpdate_User);
            }
            else if (rbtnWorkstation.Checked == true)
            {
                strSql = getSqlString_12("HR_Work_Hours_By_Workstation_Cancel", "WORKSTATION AS 工作站,", cdtWorkStation, strDate_Type, strUpdate_Time, strUpdate_User);
            }
            else if (rbtnWorkCenter.Checked == true)
            {
                strSql = getSqlString_3(cdtWorkCenter, strDate_Type, strUpdate_Time, strUpdate_User);
            }

            setGridViewData(gridControl1, gridView1, strSql);
        }

        private void copyDataAndSetData_V2(string strDate_Type)
        {
            if (rbtnSubFactory.Checked == true)
            {
                strSql = getSqlString_12("HR_Work_Hours_By_SubFactory_Cancel", "SUB_FACTORY AS 分厂,", cdtSubFactory, strDate_Type, "UPDATE_TIME", "UPDATE_USER");
            }
            else if (rbtnWorkstation.Checked == true)
            {
                strSql = getSqlString_12("HR_Work_Hours_By_Workstation_Cancel", "WORKSTATION AS 工作站,", cdtWorkStation, strDate_Type, "UPDATE_TIME", "UPDATE_USER");
            }
            else if (rbtnWorkCenter.Checked == true)
            {
                strSql = getSqlString_3(cdtWorkCenter, strDate_Type, "a.UPDATE_TIME", "a.UPDATE_USER");
            }

            setGridViewData(gridControl2, gridView2, strSql);
        }

        //获取初始查询条件
        private void getQueyCondition()
        {
            switch (strCurSubFactory)
            {
                case "生产-桌子":
                    cdtSubFactory = "SUB_FACTORY='生产-桌子'";
                    cdtWorkStation = "WORKSTATION LIKE 'D%'";
                    cdtWorkCenter = "WORK_CENTER LIKE 'D%'";
                    break;
                case "生产-椅子":
                    cdtSubFactory = "SUB_FACTORY='生产-椅子'";
                    cdtWorkStation = "WORKSTATION LIKE 'S%'";
                    cdtWorkCenter = "WORK_CENTER LIKE 'S%'";
                    break;
                case "生产-屏风":
                    cdtSubFactory = "SUB_FACTORY='生产-屏风'";
                    cdtWorkStation = "WORKSTATION LIKE 'P%'";
                    cdtWorkCenter = "WORK_CENTER LIKE 'P%'";
                    break;
                case "生产-MCG":
                    cdtSubFactory = "SUB_FACTORY='生产-MCG'";
                    cdtWorkStation = "WORKSTATION LIKE 'M%'";
                    cdtWorkCenter = "(WORK_CENTER LIKE 'M%' OR WORK_CENTER LIKE 'T0%')";
                    break;
            }
        }

        //分厂及工作站查询SQL语句
        private string getSqlString_12(string strDataTable, string strCurField, string strCondition, string strDateType, string strUpdate_Time, string strUpdate_User)
        {
            strSql = @"SELECT " + strDateType + @" AS 工作日," + strCurField + @"WORKING_HOURS AS 实际工时,
                        " + strUpdate_Time + @" AS 更新时间," + strUpdate_User + @" AS 更新用户
                        FROM " + strDataTable + @"
                        WHERE WORKING_DATE IN
                        (SELECT MAX(WORKING_DATE)
                        FROM " + strDataTable + @"
                        WHERE " + strCondition + @") 
                        AND " + strCondition;
            return strSql;
        }

        //工作中心查询SQL语句
        private string getSqlString_3(string strCondition, string strDateType, string strUpdate_Time, string strUpdate_User)
        {
            strSql = @"						SELECT " + strDateType + @" AS 工作日,WORK_CENTER AS 工作中心,
                        b.PROCESS_NAME AS 工序描述,WORKING_HOURS AS 实际工时,
                        " + strUpdate_Time + @" AS 更新时间," + strUpdate_User + @" AS 更新用户
                        FROM
						(SELECT *
                        FROM HR_Work_Hours_By_WorkCenter_Cancel
                        WHERE WORKING_DATE IN
                        (SELECT MAX(WORKING_DATE)
                        FROM HR_Work_Hours_By_WorkCenter_Cancel
                        WHERE " + strCondition + @")) AS a
						LEFT JOIN 
						IE_WorkCenter_Definition AS b
						ON a.WORK_CENTER=b.PROCESS_CODE
						WHERE " + strCondition;
            return strSql;
        }

        private void setGridViewData(GridControl gc, GridView gv, string StrSql)
        {
            DataTable dt = Lamex.Base.BaseBuildSql.GetDataTableBySql(StrSql);
            //dt.Rows.Clear();
            if (dt.Rows.Count > 0)
            {
                gc.DataSource = dt;
                //setGridViewColumnAutoWidth(gv);

                gv.OptionsView.ColumnAutoWidth = true;
                //gv.Columns[0].BestFit();
                //gv.Columns[1].BestFit();

                //gridView3.BestFitColumns();
                //setGridViewColumnAutoWidth();

                //refreshPositionStaduss();
            }
        }

        //数据写入保存
        private void btnInsert_Click(object sender, EventArgs e)
        {
            if (isExistCurDateData())
            {
                if (MessageBox.Show("数据已存在！" + "\r\n" + "此操作将删除选定日期实际工时数据！", "写入确认", MessageBoxButtons.OKCancel) == DialogResult.OK)
                {
                    goto c;
                }
                else
                {
                    return;
                }
            }
        c: ;
            DeleteExistData("HR_Work_Hours_By_SubFactory_Cancel", cdtSubFactory);
            DeleteExistData("HR_Work_Hours_By_Workstation_Cancel", cdtWorkStation);
            DeleteExistData("HR_Work_Hours_By_WorkCenter_Cancel", cdtWorkCenter);

            DataTable dt = ((DataView)gridView1.DataSource).ToTable();

            if (rbtnSubFactory.Checked == true)
            {
                SqlBulkCopyInsert("HR_Work_Hours_By_SubFactory_Cancel", dt, conStr);
                ReviseUpdateTime("HR_Work_Hours_By_SubFactory_Cancel", cdtSubFactory);
            }
            else if (rbtnWorkstation.Checked == true)
            {
                SqlBulkCopyInsert("HR_Work_Hours_By_Workstation_Cancel", dt, conStr);
                ReviseUpdateTime("HR_Work_Hours_By_Workstation_Cancel", cdtWorkStation);

                InsertData_SubFactory(cdtWorkStation);//取下一级作为过滤条件
            }
            else if (rbtnWorkCenter.Checked == true)
            {
                DataTableToSQLServer("HR_Work_Hours_By_WorkCenter_Cancel", dt, conStr);
                ReviseUpdateTime("HR_Work_Hours_By_WorkCenter_Cancel", cdtWorkCenter);

                InsertData_WorkStation(cdtWorkCenter);//取下一级作为过滤条件
                InsertData_SubFactory(cdtWorkStation);//取下一级作为过滤条件
            }
        }

        //改写写入时间
        private void ReviseUpdateTime(string strTableName, string strCondition)
        {
            strSql = @"UPDATE " + strTableName + @"
                        SET UPDATE_TIME=GETDATE()
                        WHERE " + strCondition + @"
                        AND WORKING_DATE='" + dteSelDate.Text.ToString() + @"'
                        AND UPDATE_TIME='9999-12-31'";
            ExecuteSqlOperation();
        }

        //同步插入工作站数据
        private void InsertData_WorkStation(string strCondition)
        {
            strSql = @"INSERT INTO HR_Work_Hours_By_Workstation_Cancel(WORKING_DATE,WORKSTATION,WORKING_HOURS,UPDATE_TIME,UPDATE_USER)
                        SELECT WORKING_DATE,WORKSTATION,WORKING_HOURS,UPDATE_TIME,UPDATE_USER FROM
                        (SELECT WORKING_DATE,WORKSTATION,SUM(WORKING_HOURS) AS WORKING_HOURS,GETDATE() AS UPDATE_TIME,'SysInsert' AS UPDATE_USER
                        FROM
                        (SELECT WORKING_DATE,WORKSTATION,WORK_CENTER,b.WORKING_HOURS
                        FROM
                        OP_MGT_Workstation_Process AS a
                        LEFT JOIN
                        HR_Work_Hours_By_WorkCenter_Cancel AS b
                        ON a.PROCESS_CODE=b.WORK_CENTER
                        WHERE " + strCondition + @"
                        AND WORKING_DATE='" + dteSelDate.Text.ToString() + @"'
                        ) AS c
                        GROUP BY WORKING_DATE,WORKSTATION) AS d";
            ExecuteSqlOperation();
        }

        //同步插入分厂数据
        private void InsertData_SubFactory(string strCondition)
        {
            strSql = @"INSERT INTO HR_Work_Hours_By_SubFactory_Cancel(WORKING_DATE,SUB_FACTORY,WORKING_HOURS,UPDATE_TIME,UPDATE_USER)
                        SELECT WORKING_DATE,SUB_FACTORY,WORKING_HOURS,UPDATE_TIME,UPDATE_USER FROM
                        (SELECT WORKING_DATE,SUB_FACTORY,SUM(WORKING_HOURS) AS WORKING_HOURS,GETDATE() AS UPDATE_TIME,'SysInsert' AS UPDATE_USER
                        FROM
                        (SELECT WORKING_DATE,SUB_FACTORY,b.WORKSTATION,b.WORKING_HOURS
                        FROM
                        OP_MGT_Factory_Workstation AS a
                        LEFT JOIN
                        HR_Work_Hours_By_Workstation_Cancel AS b
                        ON a.WORKSTATION=b.WORKSTATION
                        WHERE b." + strCondition + @"
                        AND WORKING_DATE='" + dteSelDate.Text.ToString() + @"'
                        ) AS c
                        GROUP BY WORKING_DATE,SUB_FACTORY) AS d";
            ExecuteSqlOperation();
        }
        //删除已有数据
        private void DeleteExistData(string strTableName, string strCondition)
        {
            strSql = @"DELETE FROM " + strTableName + @"
                        WHERE WORKING_DATE='" + dteSelDate.Text.ToString() + @"'
                        AND " + strCondition;
            ExecuteSqlOperation();
        }

        //执行SQL操作
        private void ExecuteSqlOperation()
        {
            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(strSql, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        #region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中（sql的块处理插入）
        
        //将 datatable 数据导入到 SQL Server
        //DataTable中的列需要与数据库表中的列完全一致。
        public void SqlBulkCopyInsert(string strTableName, DataTable dt, string conStr)
        {
            try
            {
                using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(conStr))//引用SqlBulkCopy 
                {
                    sqlRevdBulkCopy.DestinationTableName = strTableName;//数据库中对应的表名 
                    sqlRevdBulkCopy.NotifyAfter = dt.Rows.Count;//有几行数据 
                    sqlRevdBulkCopy.WriteToServer(dt);//数据导入数据库 
                    sqlRevdBulkCopy.Close();//关闭连接 
                    MessageBox.Show("导入成功，请前往【人员-实际工时-工时查询】界面查看！");
                    gridView1.Columns.Clear();
                    gridView2.Columns.Clear();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //DataTable中的列与数据库表中的列可以不一致
        public void DataTableToSQLServer(string strTableName, DataTable dt, string conStr)
        {
            try
            {
                using (SqlConnection cnn = new SqlConnection(conStr))
                {
                    cnn.Open();

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cnn))
                    {
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.DestinationTableName = strTableName;//要插入的表的表名
                        bulkCopy.ColumnMappings.Add("工作日", "WORKING_DATE");//映射字段名：DataTable列名 数据库中的字段名  
                        bulkCopy.ColumnMappings.Add("工作中心", "WORK_CENTER");
                        bulkCopy.ColumnMappings.Add("实际工时", "WORKING_HOURS");
                        bulkCopy.ColumnMappings.Add("更新时间", "UPDATE_TIME");
                        bulkCopy.ColumnMappings.Add("更新用户", "UPDATE_USER");
                        bulkCopy.WriteToServer(dt);
                        MessageBox.Show("导入成功，请前往【人员-实际工时-工时查询】界面查看！");
                        gridView1.Columns.Clear();
                        gridView2.Columns.Clear();
                    }
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        private DataTable GetGridViewData(DataTable table)
        {
            table.Columns.Add(new DataColumn("ITEM_NAME"));
            table.Columns.Add(new DataColumn("REQUIRED_QTY"));
            table.Columns.Add(new DataColumn("SIMULATED_QTY"));
            table.Columns.Add(new DataColumn("ISSUED_QTY"));
            table.Columns.Add(new DataColumn("ISSUE_WO_NO"));
            table.Columns.Add(new DataColumn("PICKUP_QTY"));
            table.Columns.Add(new DataColumn("FRAME_KEY"));
            //foreach (GridViewRow row in gridView1.Rows)
            //{
            //    DataRow sourseRow = table.NewRow();
            //    sourseRow["ITEM_NAME"] = row.Cells[0].Text;
            //    sourseRow["REQUIRED_QTY"] = row.Cells[1].Text;
            //    sourseRow["SIMULATED_QTY"] = row.Cells[2].Text;
            //    sourseRow["ISSUED_QTY"] = row.Cells[3].Text;
            //    sourseRow["ISSUE_WO_NO"] = row.Cells[4].Text;
            //    sourseRow["PICKUP_QTY"] = row.Cells[5].Text;
            //    sourseRow["FRAME_KEY"] = row.Cells[6].Text;
            //    table.Rows.Add(sourseRow);
            //}
            return table;
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            gridView1.Columns.Clear();
            gridView2.Columns.Clear();
        }

        private void dteSelDate_EditValueChanged(object sender, EventArgs e)
        {
            gridView1.Columns.Clear();
            gridView2.Columns.Clear();
        }

        private void 隐藏选项区ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            panelTop.Height = 3;
        }

        private void 显示选项区ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            panelTop.Height = 84;
        }

        //gridView仅复制单元格内容，复制选中行焦点单元格的值
        private void gridView1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Control & e.KeyCode == Keys.C)
            {
                Clipboard.SetDataObject(gridView1.GetFocusedRowCellValue(gridView1.FocusedColumn));
                e.Handled = true;
            }

            //或设置属性
            //optionBehavior - CopyToClipboardWithColumnHeader = false;
            //optionSelection - MultiSelectMode = CellSelect;
        }
    }
}